﻿using BCCommon;
using BCCommon.Enums;
using BCCommon.Enums.Driver;
using BCCommon.TMS.Driver;
using BCCommon.TMS.Order;
using BCEntity.TMS.Driver;
using BCEntity.TMS.KanBan.AgentKanBan;
using BCEntity.Website.User;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.Driver
{
    /// <summary>
    /// 司机信息处理
    /// </summary>
    public class DriverData : IDriverData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT DriverId,Phone,Name,Sex,VehicleNo,UserId,VehicleTypeId,VehicleType,DriverLicense,DriverLicenseDeputyPage,VehicleLicense,VehicleLicenseDeputyPage,DriverPhotoPath,VehiclePhotoFrontPath,VehiclePhotoBackPath,CarInsurancePolicy,BusinessInsurance,
            IsAcceptAssign,IsReceivePartLoad,IdentityCardNo,IdentityCardPath,IdentityCardBackPath,DriverStatus,RefuseToReason,WorkingStatus,CompanyId,EmergencyPerson,EmergencyPhone,DriverLevel,Income,IsTrained,IsTrainPassed,TrainPassedTime,PersonCarPhoto,CreditScore,
            CreateTime,ApproveTime,Approver,ApproverId,EditorId,Editor,EditTime,IsUseBillingRules,Source,Password,ProvinceId,ProvinceCode,ProvinceName,CityId,CityCode,CityName,CountyId,CountyCode,CountyName,VehicleLength,VehicleWidth,VehicleHeight,VehicleWeight,VehicleStere,PersonalInsuranceRecordId,ElectricFenceId,
            IsGuaranteedProfit,DeliveryBoxLength,DeliveryBoxWidth,DeliveryBoxHeight,Role,IsRealNameVerified,IsFaceVerified,RealName,FacePic,RealNameVerifiesStatus,RealNameVerifiesRefuse,FaceVerifiesRefuse,HealthCertificateType,HealthCertificate,AgentRecordUuid,
            IsLearnTrainPassed,LearnTrainPassedTime,WorkingCompanyId,WorkingCompanyName,AgentRecordId,OneAgentRecordId,TwoAgentRecordId,IsLearnTrainSign FROM Driver  ";

        public DriverData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }


        #region 添加司机信息
        public async Task<DriverEntity> InsertAsync(DriverEntity driverEntity, string passCode = null, string ip = null, string addressIp = null)
        {
            string insertUserSql = @"INSERT INTO User(Phone,Password,UserName,AccountStatus,Role,PassCode,LogonIp,AddressIp,UniqueCode)
                                VALUES(?Phone,?Password,?UserName,?AccountStatus,?Role,?PassCode,?LogonIp,?AddressIp,UUID_SHORT());
                            SELECT  UserId,Phone,UserName FROM User WHERE UserId=LAST_INSERT_ID();";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?Phone", driverEntity.Phone, DbType.AnsiString, size: 20);
            parameter.Add("?Password", driverEntity.Password, DbType.AnsiString, size: 255);
            parameter.Add("?UserName", driverEntity.Name, DbType.AnsiString, size: 20);
            parameter.Add("?AccountStatus", AccountStatus.Using.ToString(), DbType.AnsiString);
            parameter.Add("?Role", RoleType.Driver.ToString(), DbType.AnsiString, size: 30);
            parameter.Add("?PassCode", passCode, DbType.AnsiString, size: 10);
            parameter.Add("?LogonIp", ip, DbType.AnsiString, size: 50);
            parameter.Add("?AddressIp", addressIp, DbType.AnsiString, size: 50);

            var user = await this.databaseContext.Master.QueryFirstOrDefaultAsync<UserEntity>(insertUserSql, parameter, databaseContext.Transaction);
            if (user != null)
            {
                string sql = SqlCommands.GetInsertSql("Driver", new[] {"Phone","Name","UserId","Sex","VehicleNo","VehicleType","VehicleTypeId","VehicleWeight", "VehicleLength", "VehicleWidth", "VehicleHeight", "VehicleStere",
            "DriverLicense","DriverLicenseDeputyPage","VehicleLicense","VehicleLicenseDeputyPage","DriverPhotoPath","VehiclePhotoFrontPath","VehiclePhotoBackPath","CarInsurancePolicy","BusinessInsurance","IsAcceptAssign","IsReceivePartLoad","IdentityCardNo","IdentityCardPath",
            "IdentityCardBackPath","WorkingStatus","CompanyId","EmergencyPerson","EmergencyPhone","IsTrained","IsTrainPassed","TrainPassedTime","PersonCarPhoto","Source","Password","ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId", "CountyCode", "CountyName","PersonalInsuranceRecordId","ElectricFenceId","DeliveryBoxLength","DeliveryBoxWidth","DeliveryBoxHeight",
            "Role","IsRealNameVerified","RealNameVerifiesRefuse","RealNameVerifiesStatus","IsFaceVerified","RealName","FacePic","FaceVerifiesRefuse","HealthCertificateType","HealthCertificate","AgentRecordUuid","WorkingCompanyId","WorkingCompanyName","AgentRecordId","OneAgentRecordId","TwoAgentRecordId"});
                sql = string.Format(@"{0} {1} WHERE DriverId=LAST_INSERT_ID();", sql, QUERY);
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?Phone", driverEntity.Phone, DbType.AnsiString, size: 20);
                parameters.Add("?Name", driverEntity.Name, DbType.AnsiString, size: 20);
                parameters.Add("?Sex", driverEntity.Sex.ToString(), DbType.AnsiString, size: 20);
                parameters.Add("?UserId", user.UserId, DbType.Int32, size: 20);
                parameters.Add("?VehicleNo", driverEntity.VehicleNo, DbType.AnsiString, size: 20);
                parameters.Add("?VehicleType", driverEntity.VehicleType, DbType.AnsiString, size: 20);
                parameters.Add("?VehicleTypeId", driverEntity.VehicleTypeId, DbType.Int32, size: 11);
                parameters.Add("?VehicleWeight", driverEntity.VehicleWeight, DbType.Int32);
                parameters.Add("?VehicleLength", driverEntity.VehicleLength, DbType.Decimal);
                parameters.Add("?VehicleWidth", driverEntity.VehicleWidth, DbType.Decimal);
                parameters.Add("?VehicleHeight", driverEntity.VehicleHeight, DbType.Decimal);
                parameters.Add("?VehicleStere", driverEntity.VehicleStere, DbType.Decimal);
                parameters.Add("?DriverLicense", driverEntity.DriverLicense, DbType.AnsiString, size: 1000);
                parameters.Add("?DriverLicenseDeputyPage", driverEntity.DriverLicenseDeputyPage, DbType.AnsiString, size: 1000);
                parameters.Add("?VehicleLicense", driverEntity.VehicleLicense, DbType.AnsiString, size: 1000);
                parameters.Add("?VehicleLicenseDeputyPage", driverEntity.VehicleLicenseDeputyPage, DbType.AnsiString, size: 1000);
                parameters.Add("?DriverPhotoPath", driverEntity.DriverPhotoPath, DbType.AnsiString, size: 1000);
                parameters.Add("?VehiclePhotoFrontPath", driverEntity.VehiclePhotoFrontPath, DbType.AnsiString, size: 1000);
                parameters.Add("?VehiclePhotoBackPath", driverEntity.VehiclePhotoBackPath, DbType.AnsiString, size: 1000);
                parameters.Add("?CarInsurancePolicy", driverEntity.CarInsurancePolicy, DbType.AnsiString, size: 1000);
                parameters.Add("?BusinessInsurance", driverEntity.BusinessInsurance, DbType.AnsiString, size: 1000);
                parameters.Add("?IsAcceptAssign", driverEntity.IsAcceptAssign, DbType.Boolean);
                parameters.Add("?IsReceivePartLoad", driverEntity.IsReceivePartLoad, DbType.Boolean);
                parameters.Add("?IdentityCardNo", driverEntity.IdentityCardNo, DbType.AnsiString, size: 18);
                parameters.Add("?IdentityCardPath", driverEntity.IdentityCardPath, DbType.AnsiString, size: 1000);
                parameters.Add("?IdentityCardBackPath", driverEntity.IdentityCardBackPath, DbType.AnsiString, size: 1000);
                parameters.Add("?WorkingStatus", driverEntity.WorkingStatus.ToString(), DbType.AnsiString, size: 20);
                parameters.Add("?CompanyId", driverEntity.CompanyId, DbType.Int64);
                parameters.Add("?EmergencyPerson", driverEntity.EmergencyPerson, DbType.AnsiString, size: 20);
                parameters.Add("?EmergencyPhone", driverEntity.EmergencyPhone, DbType.AnsiString, size: 20);
                parameters.Add("?IsTrained", driverEntity.IsTrained, DbType.Boolean);
                parameters.Add("?IsTrainPassed", driverEntity.IsTrainPassed, DbType.Boolean);
                parameters.Add("?TrainPassedTime", driverEntity.TrainPassedTime, DbType.DateTime);
                parameters.Add("?PersonCarPhoto", driverEntity.PersonCarPhoto, DbType.AnsiString, size: 1000);
                parameters.Add("?Source", driverEntity.Source.ToString(), DbType.AnsiString, size: 20);
                parameters.Add("?Password", driverEntity.Password, DbType.AnsiString, size: 255);
                parameters.Add("?ProvinceCode", driverEntity.ProvinceCode, DbType.AnsiString, size: 20);
                parameters.Add("?CityCode", driverEntity.CityCode, DbType.AnsiString, size: 20);
                parameters.Add("?CountyCode", driverEntity.CountyCode, DbType.AnsiString, size: 20);
                parameters.Add("?PersonalInsuranceRecordId", driverEntity.PersonalInsuranceRecordId, DbType.Int64);
                parameters.Add("?ElectricFenceId", driverEntity.ElectricFenceId, DbType.Int64);
                parameters.Add("?IsGuaranteedProfit", driverEntity.IsGuaranteedProfit, DbType.Boolean);
                parameters.Add("?DeliveryBoxLength", driverEntity.DeliveryBoxLength, DbType.Decimal);
                parameters.Add("?DeliveryBoxHeight", driverEntity.DeliveryBoxHeight, DbType.Decimal);
                parameters.Add("?DeliveryBoxWidth", driverEntity.DeliveryBoxWidth, DbType.Decimal);
                parameters.Add("?Role", driverEntity.Role, DbType.AnsiString);
                parameters.Add("?IsRealNameVerified", driverEntity.IsRealNameVerified, DbType.Boolean);
                parameters.Add("?IsFaceVerified", driverEntity.IsFaceVerified, DbType.Boolean);
                parameters.Add("?RealName", driverEntity.RealName, DbType.AnsiString, size: 20);
                parameters.Add("?FaceVerifiesRefuse", driverEntity.FaceVerifiesRefuse, DbType.AnsiString, size: 400);
                parameters.Add("?RealNameVerifiesRefuse", driverEntity.RealNameVerifiesRefuse, DbType.AnsiString, size: 400);
                parameters.Add("?RealNameVerifiesStatus", driverEntity.RealNameVerifiesStatus, DbType.AnsiString, size: 20);
                parameters.Add("?FacePic", driverEntity.FacePic, DbType.AnsiString, size: 20);
                parameters.Add("?HealthCertificateType", driverEntity.HealthCertificateType.HasValue ? driverEntity.HealthCertificateType.Value.ToString() : null, DbType.AnsiString, size: 20);
                parameters.Add("?HealthCertificate", driverEntity.HealthCertificate, DbType.AnsiString, size: 1000);
                parameters.Add("?ProvinceId", driverEntity.ProvinceId, DbType.Int64);
                parameters.Add("?ProvinceCode", driverEntity.ProvinceCode, DbType.AnsiString, size: 20);
                parameters.Add("?ProvinceName", driverEntity.ProvinceName, DbType.AnsiString, size: 50);
                parameters.Add("?CityId", driverEntity.CityId, DbType.Int64);
                parameters.Add("?CityCode", driverEntity.CityCode, DbType.AnsiString, size: 20);
                parameters.Add("?CityName", driverEntity.CityName, DbType.AnsiString, size: 50);
                parameters.Add("?CountyId", driverEntity.CountyId, DbType.Int64);
                parameters.Add("?CountyCode", driverEntity.CountyCode, DbType.AnsiString, size: 20);
                parameters.Add("?CountyName", driverEntity.CountyName, DbType.AnsiString, size: 50);
                parameters.Add("?AgentRecordUuid", driverEntity.AgentRecordUuid, DbType.AnsiString, size: 100);
                parameters.Add("?WorkingCompanyId", driverEntity.WorkingCompanyId, DbType.Int64);
                parameters.Add("?WorkingCompanyName", driverEntity.WorkingCompanyName, DbType.AnsiString, size: 20);
                parameters.Add("?AgentRecordId", driverEntity.AgentRecordId, DbType.Int64);
                parameters.Add("?OneAgentRecordId", driverEntity.OneAgentRecordId, DbType.Int64);
                parameters.Add("?TwoAgentRecordId", driverEntity.TwoAgentRecordId, DbType.Int64);
                return await databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
            }
            return null;
        }
        #endregion


        #region 检查信息
        /// <summary>
        /// 检查司机是否已存在
        /// </summary>
        /// <param name="phone">手机号</param>
        /// <returns></returns>
        public async Task<bool> CheckDriverExistsByPhoneAsync(string phone)
        {
            string sql = @"SELECT 1 FROM Driver WHERE Phone=?Phone;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Phone", phone, DbType.AnsiString, size: 30);
            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        /// <summary>
        /// 检查司机是否已存在
        /// </summary>
        /// <param name="vehicleNo">车牌号</param>
        /// <returns></returns>
        public async Task<bool> CheckDriverExistsByVehicleNoAsync(string vehicleNo)
        {
            string sql = @"SELECT 1 FROM Driver WHERE VehicleNo=?VehicleNo;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString, size: 30);
            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 查询信息
        /// <summary>
        /// 获取最大的UserId
        /// </summary>
        /// <returns></returns>
        public async Task<int> GetMaxUserIdAsync()
        {
            string sql = "SELECT MAX(UserId) FROM Driver LIMIT 1;";
            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql);
        }


        /// <summary>
        /// 获取司机信息列表（分页）
        /// </summary>
        /// <param name="companyId">总公司/代理商Id</param>
        /// <param name="phone">手机</param>
        /// <param name="name">司机名</param>
        /// <param name="vehicleNo">车牌号</param>
        /// <param name="provinceCode">省编码</param>
        /// <param name="cityCode">市编码</param>
        /// <param name="countyCode">区县编码</param>
        /// <param name="driverStatus">审核状态</param>       
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>       
        /// <param name="ascending">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页条数</param>
        public async Task<(IEnumerable<DriverEntity>, int)> GetDriversAsync(long? companyId, string phone, string name, string vehicleNo, string provinceCode, string cityCode, string countyCode, DriverStatus? driverStatus, DriverRoleType? role, DateTime? start, DateTime? end, bool? ascending, string sortName, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (companyId.HasValue)
            {
                wheres.Add("CompanyId =?CompanyId ");
                parameters.Add("?CompanyId", companyId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add("Phone LIKE CONCAT('%',CONCAT(?Phone,'%'))");
                parameters.Add("?Phone", phone.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(name))
            {
                wheres.Add("Name LIKE CONCAT('%',CONCAT(?Name,'%'))");
                parameters.Add("?Name", name.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(vehicleNo))
            {
                wheres.Add("VehicleNo LIKE CONCAT('%',CONCAT(?VehicleNo,'%'))");
                parameters.Add("?VehicleNo", vehicleNo.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode =?ProvinceCode ");
                parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("CityCode =?CityCode");
                parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode =?CountyCode ");
                parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            }
            if (driverStatus.HasValue)
            {
                wheres.Add("DriverStatus =?DriverStatus");
                parameters.Add("?DriverStatus", driverStatus.Value, DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            if (role.HasValue)
            {
                wheres.Add("Role=?Role");
                parameters.Add("?Role", role.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format("{0} {1} ORDER BY {4} {5} LIMIT {2}, {3}; SELECT COUNT(1) FROM Driver {1};",
                QUERY,
                 wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await databaseContext.Slave.QueryMultipleAsync<DriverEntity, int>(sql, parameters);
        }


        public async Task<IEnumerable<DriverEntity>> GetALLApprovedDriversAsync(long? companyId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("RealNameVerifiesStatus =?RealNameVerifiesStatus ");
            parameters.Add("?RealNameVerifiesStatus", RealNameVerifiesStatus.Success.ToString(), DbType.AnsiString);
            wheres.Add("DriverStatus =?DriverStatus ");
            parameters.Add("?DriverStatus", DriverStatus.Success.ToString(), DbType.AnsiString);
            if (companyId.HasValue)
            {
                wheres.Add("CompanyId =?CompanyId ");
                parameters.Add("?CompanyId", companyId.Value, DbType.Int64);
            }
            string sql = string.Format("{0}  {1}", QUERY, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await databaseContext.Slave.QueryAsync<DriverEntity>(sql, parameters);
        }

        /// <summary>
        /// 根据手机号查询司机信息
        /// </summary>
        /// <param name="phone">手机号</param>
        /// <returns></returns>
        public async Task<DriverEntity> GetDriverByPhoneAsync(string phone)
        {
            string sql = string.Format(@"{0} WHERE Phone=?Phone LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Phone", phone, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters);
        }
        /// <summary>
        /// 根据司机Id查询司机信息
        /// </summary>
        /// <param name="driverId">司机Id</param>
        /// <returns></returns>
        public async Task<DriverEntity> GetDriverByIdAsync(long driverId)
        {
            string sql = string.Format(@"{0} WHERE DriverId=?DriverId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", driverId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters);
        }

        /// <summary>
        /// 根据用户Id查询司机信息
        /// </summary>
        /// <param name="driverId">司机Id</param>
        /// <returns></returns>
        public async Task<DriverEntity> GetDriverByUserIdAsync(long userId)
        {
            string sql = string.Format(@"{0} WHERE UserId=?UserId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters);
        }
        /// <summary>
        /// 根据用户Id查询司机信息
        /// </summary>
        /// <param name="driverId">司机Id</param>
        /// <returns></returns>
        public async Task<DriverEntity> GetDriverByVehicleNoAsync(string vehicleNo)
        {
            string sql = string.Format(@"{0} WHERE VehicleNo=?VehicleNo LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters);
        }

        /// <summary>
        /// 根据手机号和密码查询司机信息
        /// </summary>
        /// <param name="phone">登录名</param>
        /// <param name="paswword">密码</param>
        /// <returns></returns>
        public async Task<DriverEntity> GetDriverByPhoneAndPasswordAsync(string phone, string paswword)
        {
            string sql = string.Format(@"{0} WHERE Phone=?Phone AND Paswword=?Paswword LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Phone", phone, DbType.AnsiString);
            parameters.Add("?Paswword", paswword, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters);
        }

        public async Task<DriverEntity> GetDriverByIdentityCardNoAsync(string identityCardNo)
        {
            string sql = string.Format(@"{0} WHERE IdentityCardNo=?IdentityCardNo LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IdentityCardNo", identityCardNo, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters);
        }

        #endregion

        #region 修改司机信息
        public async Task<DriverEntity> UpdateAsync(DriverEntity driverEntity)
        {
            return await this.databaseContext.Master.UpdateAsync(driverEntity);
        }
        #endregion

        #region 实名认证
        /// <summary>
        /// 实名认证
        /// </summary>
        /// <param name="entity">司机实名信息</param>
        /// <returns></returns>
        public async Task<DriverEntity> RealNameVerifiesAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "RealName", "IdentityCardNo", "IdentityCardPath", "IdentityCardBackPath", "IsRealNameVerified" }, new[] { "DriverId" });

            sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?RealName", entity.RealName, DbType.AnsiString, size: 21);
            parameters.Add("?IdentityCardNo", entity.IdentityCardNo, DbType.AnsiString, size: 1000);
            parameters.Add("?IdentityCardPath", entity.IdentityCardPath, DbType.AnsiString, size: 1000);
            parameters.Add("?IdentityCardBackPath", entity.IdentityCardPath, DbType.AnsiString, size: 1000);
            parameters.Add("?IsRealNameVerified", entity.IsRealNameVerified, DbType.Boolean);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }

        /// <summary>
        /// 实名认证审核
        /// </summary>
        /// <param name="entity">实名认证审核信息</param>
        /// <returns></returns>
        public async Task<DriverEntity> RealNameVerifiesApproveAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "IsRealNameVerified", "RealNameVerifiesRefuse", "RealNameVerifiesStatus", "Approver", "ApproverId", "ApproveTime" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?IsRealNameVerified", entity.IsRealNameVerified, DbType.Boolean);
            parameters.Add("?RealNameVerifiesRefuse", entity.RealNameVerifiesRefuse, DbType.AnsiString);
            parameters.Add("?RealNameVerifiesStatus", entity.RealNameVerifiesStatus, DbType.AnsiString);
            parameters.Add("?Approver", entity.Approver, DbType.AnsiString, size: 20);
            parameters.Add("?ApproverId", entity.ApproverId, DbType.Int64);
            parameters.Add("?ApproveTime", entity.ApproverId, DbType.DateTime, size: 400);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 人脸识别

        /// <summary>
        /// 人脸识别
        /// </summary>
        /// <param name="entity">人脸识别信息</param>
        /// <returns></returns>
        public async Task<DriverEntity> FaceVerifiesAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "FacePic" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?FacePic", entity.IdentityCardNo, DbType.AnsiString, size: 1000);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }

        /// <summary>
        /// 人脸识别认证
        /// </summary>
        /// <param name="entity">人脸识别认证审核信息</param>
        /// <returns></returns>
        public async Task<DriverEntity> FaceVerifiesApproveAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "IsFaceVerified", "FaceVerifiesRefuse", "Approver", "ApproverId", "ApproverId" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?IsFaceVerified", entity.IsFaceVerified, DbType.Boolean);
            parameters.Add("?FaceVerifiesRefuse", entity.FaceVerifiesRefuse, DbType.AnsiString);
            parameters.Add("?Approver", entity.Approver, DbType.AnsiString, size: 20);
            parameters.Add("?ApproverId", entity.ApproverId, DbType.Int64);
            parameters.Add("?ApproveTime", entity.ApproveTime, DbType.DateTime, size: 400);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 车辆信息
        /// <summary>
        /// 更新车辆信息
        /// </summary>
        /// <param name="entity">更新车辆信息</param>
        /// <returns></returns>
        public async Task<DriverEntity> UpdateVehicleAsync(DriverEntity driverEntity)
        {
            DynamicParameters parameters = new DynamicParameters();
            string updateUserSql = SqlCommands.GetUpdateSql("User", new[] { "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId", "CountyCode", "CountyName" }, new[] { "UserId" });
            string queryUserSql = string.Format("{0} SELECT  UserId,Phone,UserName FROM User WHERE UserId=?UserId; ", updateUserSql);
            parameters.Add("?UserId", driverEntity.UserId, DbType.Int64);
            parameters.Add("?ProvinceId", driverEntity.ProvinceId, DbType.Int64);
            parameters.Add("?ProvinceCode", driverEntity.ProvinceCode, DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", driverEntity.ProvinceName, DbType.AnsiString, size: 50);
            parameters.Add("?CityId", driverEntity.CityId, DbType.Int64);
            parameters.Add("?CityCode", driverEntity.CityCode, DbType.AnsiString, size: 20);
            parameters.Add("?CityName", driverEntity.CityName, DbType.AnsiString, size: 50);
            parameters.Add("?CountyId", driverEntity.CountyId, DbType.Int64);
            parameters.Add("?CountyCode", driverEntity.CountyCode, DbType.AnsiString, size: 20);
            parameters.Add("?CountyName", driverEntity.CountyName, DbType.AnsiString, size: 50);
            var user = await this.databaseContext.Master.QueryFirstOrDefaultAsync<UserEntity>(queryUserSql, parameters, databaseContext.Transaction);
            if (user != null)
            {
                string sql = SqlCommands.GetUpdateSql("Driver", new[] { "DriverStatus", "VehicleTypeId", "VehicleType", "VehicleNo", "VehicleLength", "VehicleWidth", "VehicleHeight", "VehicleWeight", "VehicleStere", "DriverLicense", "VehicleLicense", "VehiclePhotoFrontPath", "VehiclePhotoBackPath", "CarInsurancePolicy", "BusinessInsurance", "PersonalInsuranceRecordId", "Role", "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId", "CountyCode", "CountyName" }, new[] { "DriverId" });
                sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
                parameters.Add("?Role", driverEntity.Role, DbType.AnsiString, size: 20);
                parameters.Add("?DriverId", driverEntity.DriverId, DbType.Int32, size: 20);
                parameters.Add("?VehicleType", driverEntity.VehicleType, DbType.AnsiString, size: 20);
                parameters.Add("?VehicleTypeId", driverEntity.VehicleTypeId, DbType.Int32, size: 11);
                parameters.Add("?VehicleNo", driverEntity.VehicleNo, DbType.AnsiString, size: 20);
                parameters.Add("?VehicleLength", driverEntity.VehicleLength, DbType.Decimal);
                parameters.Add("?VehicleWidth", driverEntity.VehicleWidth, DbType.Decimal);
                parameters.Add("?VehicleHeight", driverEntity.VehicleHeight, DbType.Decimal);
                parameters.Add("?VehicleWeight", driverEntity.VehicleWeight, DbType.Decimal);
                parameters.Add("?VehicleStere", driverEntity.VehicleStere, DbType.Decimal);
                parameters.Add("?DriverLicense", driverEntity.DriverLicense, DbType.AnsiString, size: 1000);
                parameters.Add("?VehicleLicense", driverEntity.VehicleLicense, DbType.AnsiString, size: 1000);
                parameters.Add("?VehiclePhotoFrontPath", driverEntity.VehiclePhotoFrontPath, DbType.AnsiString, size: 1000);
                parameters.Add("?VehiclePhotoBackPath", driverEntity.VehiclePhotoBackPath, DbType.AnsiString, size: 1000);
                parameters.Add("?CarInsurancePolicy", driverEntity.CarInsurancePolicy, DbType.AnsiString, size: 1000);
                parameters.Add("?BusinessInsurance", driverEntity.BusinessInsurance, DbType.AnsiString, size: 1000);
                parameters.Add("?PersonalInsuranceRecordId", driverEntity.PersonalInsuranceRecordId, DbType.Int32, size: 20);
                parameters.Add("?ProvinceCode", driverEntity.ProvinceCode, DbType.AnsiString, size: 20);
                parameters.Add("?CityCode", driverEntity.CityCode, DbType.AnsiString, size: 20);
                parameters.Add("?CountyCode", driverEntity.CountyCode, DbType.AnsiString, size: 20);
                parameters.Add("?DriverStatus", driverEntity.DriverStatus.ToString(), DbType.AnsiString);
                return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
            }
            return null;
        }


        /// <summary>
        /// 更新电动车辆信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<DriverEntity> UpdateElectricVehicleAsync(DriverEntity driverEntity)
        {
            string updateUserSql = SqlCommands.GetUpdateSql("User", new[] { "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId", "CountyCode", "CountyName" }, new[] { "UserId" });
            string queryUserSql = string.Format("{0} SELECT  UserId,Phone,UserName FROM User WHERE UserId=?UserId; ", updateUserSql);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", driverEntity.UserId, DbType.Int64);
            parameters.Add("?ProvinceId", driverEntity.ProvinceId, DbType.Int64);
            parameters.Add("?ProvinceCode", driverEntity.ProvinceCode, DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", driverEntity.ProvinceName, DbType.AnsiString, size: 50);
            parameters.Add("?CityId", driverEntity.CityId, DbType.Int64);
            parameters.Add("?CityCode", driverEntity.CityCode, DbType.AnsiString, size: 20);
            parameters.Add("?CityName", driverEntity.CityName, DbType.AnsiString, size: 50);
            parameters.Add("?CountyId", driverEntity.CountyId, DbType.Int64);
            parameters.Add("?CountyCode", driverEntity.CountyCode, DbType.AnsiString, size: 20);
            parameters.Add("?CountyName", driverEntity.CountyName, DbType.AnsiString, size: 50);
            var user = await this.databaseContext.Master.QueryFirstOrDefaultAsync<UserEntity>(queryUserSql, parameters, databaseContext.Transaction);
            if (user != null)
            {
                string sql = SqlCommands.GetUpdateSql("Driver", new[] { "DriverStatus", "VehicleTypeId", "VehicleType", "VehicleNo", "DeliveryBoxLength", "DeliveryBoxWidth", "DeliveryBoxHeight", "ElectricFenceId", "PersonalInsuranceRecordId", "Role", "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId", "CountyCode", "CountyName", "WorkingCompanyId", "WorkingCompanyName", "AgentRecordId", "OneAgentRecordId", "TwoAgentRecordId" }, new[] { "DriverId" });
                sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
                parameters.Add("?VehicleNo", driverEntity.VehicleNo, DbType.AnsiString, size: 20);
                parameters.Add("?VehicleType", driverEntity.VehicleType, DbType.AnsiString, size: 20);
                parameters.Add("?VehicleTypeId", driverEntity.VehicleTypeId, DbType.Int32, size: 11);
                parameters.Add("?DriverId", driverEntity.DriverId, DbType.Int32, size: 20);
                parameters.Add("?CountyCode", driverEntity.CountyCode, DbType.AnsiString, size: 20);
                parameters.Add("?DeliveryBoxLength", driverEntity.DeliveryBoxLength, DbType.Decimal);
                parameters.Add("?DeliveryBoxHeight", driverEntity.DeliveryBoxHeight, DbType.Decimal);
                parameters.Add("?DeliveryBoxWidth", driverEntity.DeliveryBoxWidth, DbType.Decimal);
                parameters.Add("?ElectricFenceId", driverEntity.ElectricFenceId, DbType.Int64);
                parameters.Add("?Role", driverEntity.Role, DbType.AnsiString, size: 20);
                parameters.Add("?PersonalInsuranceRecordId", driverEntity.PersonalInsuranceRecordId, DbType.Int32, size: 20);
                parameters.Add("?Role", driverEntity.Role, DbType.AnsiString, size: 20);
                parameters.Add("?DriverStatus", driverEntity.DriverStatus.ToString(), DbType.AnsiString);
                parameters.Add("?WorkingCompanyId", driverEntity.WorkingCompanyId, DbType.Int64);
                parameters.Add("?WorkingCompanyName", driverEntity.WorkingCompanyName, DbType.AnsiString, size: 20);
                parameters.Add("?AgentRecordId", driverEntity.AgentRecordId, DbType.Int64);
                parameters.Add("?OneAgentRecordId", driverEntity.OneAgentRecordId, DbType.Int64);
                parameters.Add("?TwoAgentRecordId", driverEntity.TwoAgentRecordId, DbType.Int64);
                return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
            }
            return null;
        }

        /// <summary>
        /// 车辆审核
        /// </summary>
        /// <param name="entity">车辆审核信息</param>
        /// <returns></returns>
        public async Task<DriverEntity> VehicleApproveAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "DriverStatus", "RefuseToReason", "Approver", "ApproverId", "ApproveTime" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?DriverStatus", entity.DriverStatus.ToString(), DbType.AnsiString);
            parameters.Add("?RefuseToReason", entity.RefuseToReason, DbType.AnsiString, size: 400);
            parameters.Add("?Approver", entity.Approver, DbType.AnsiString, size: 20);
            parameters.Add("?ApproverId", entity.ApproverId, DbType.Int64);
            parameters.Add("?ApproveTime", entity.ApproveTime, DbType.DateTime, size: 400);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }

        #endregion

        #region 工作城市
        /// <summary>
        /// 切换城市
        /// </summary>
        /// <param name="entity">更新城市信息</param>
        /// <returns></returns>

        public async Task<DriverEntity> ChangeCityAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId", "CountyCode", "CountyName" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?ProvinceId", entity.ProvinceId, DbType.Int64);
            parameters.Add("?ProvinceCode", entity.ProvinceCode, DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", entity.ProvinceName, DbType.AnsiString, size: 50);
            parameters.Add("?CityId", entity.CityId, DbType.Int64);
            parameters.Add("?CityCode", entity.CityCode, DbType.AnsiString, size: 20);
            parameters.Add("?CityName", entity.CityName, DbType.AnsiString, size: 50);
            parameters.Add("?CountyId", entity.CountyId, DbType.Int64);
            parameters.Add("?CountyCode", entity.CountyCode, DbType.AnsiString, size: 20);
            parameters.Add("?CountyName", entity.CountyName, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion


        #region 健康证信息

        /// <summary>
        /// 更新健康证信息
        /// </summary>
        /// <param name="driverEntity">健康证信息</param>
        /// <returns></returns>
        public async Task<DriverEntity> UpdateHealthCertificateAsync(DriverEntity driverEntity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "HealthCertificateType", "HealthCertificate" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", driverEntity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?HealthCertificateType", driverEntity.HealthCertificateType.ToString(), DbType.AnsiString, size: 20);
            parameters.Add("?HealthCertificate", driverEntity.HealthCertificate, DbType.AnsiString, size: 20);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion


        #region 培训考试
        /// <summary>
        /// 司机培训考试通过
        /// </summary>
        /// <param name="driverId"></param>
        /// <param name=""></param>
        /// <returns></returns>
        public async Task<DriverEntity> TrainPassedAsync(long driverId)
        {
            string sql = string.Format(@"UPDATE Driver SET  IsTrainPassed=?IsTrainPassed,TrainPassedTime=?TrainPassedTime  
                                  WHERE DriverId=?DriverId ;{0} WHERE DriverId=?DriverId ;", QUERY);

            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?IsTrainPassed", true, DbType.Boolean);
            parameter.Add("?TrainPassedTime", DateTime.Now, DbType.DateTime);
            parameter.Add("?DriverId", driverId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameter, databaseContext.Transaction);
        }

        /// <summary>
        /// 司机培训考试不通过
        /// </summary>
        /// <param name="driverId"></param>
        /// <param name=""></param>
        /// <returns></returns>

        public async Task<DriverEntity> TrainUnPassedAsync(long driverId)
        {
            string sql = string.Format(@"UPDATE Driver SET  IsTrainPassed=?IsTrainPassed 
                                  WHERE DriverId=?DriverId ;{0} WHERE DriverId=?DriverId ;", QUERY);

            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?IsTrainPassed", false, DbType.Boolean);
            parameter.Add("?DriverId", driverId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameter, databaseContext.Transaction);
        }

        #endregion


        public async Task<bool> UpdatePassword(DriverEntity driverEntity)
        {
            string sql = @"UPDATE Driver SET  Password=?Password  WHERE DriverId=?DriverId";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?Password", driverEntity.Password, DbType.AnsiString, size: 255);
            parameter.Add("?DriverId", driverEntity.DriverId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameter, databaseContext.Transaction) > 0;
        }

        public async Task<DriverEntity> ChangeWorkingStatusAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "WorkingStatus" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId LIMIT 1;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?WorkingStatus", entity.WorkingStatus.ToString(), DbType.AnsiString, size: 20);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<bool> UpdateAvatarAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "DriverPhotoPath" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId LIMIT 1;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?DriverPhotoPath", entity.DriverPhotoPath, DbType.AnsiString, size: 1000);
            var driver = await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
            if (driver != null)
            {
                string updateUserSql = SqlCommands.GetUpdateSql("User", new[] { "Avatar" }, new[] { "UserId" });
                parameters.Add("?UserId", entity.UserId, DbType.Int64);
                parameters.Add("?Avatar", entity.DriverPhotoPath, DbType.AnsiString, size: 255);
                return await this.databaseContext.Master.ExecuteAsync(updateUserSql, parameters, databaseContext.Transaction) > 0;
            }
            return false;
        }

        public async Task<bool> UpdatePetnameAsync(DriverEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("Driver", new[] { "Name" }, new[] { "DriverId" });
            sql = string.Format("{0} {1} WHERE DriverId=?DriverId LIMIT 1;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", entity.DriverId, DbType.Int32, size: 20);
            parameters.Add("?Name", entity.Name, DbType.AnsiString, size: 20);
            var driver = await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameters, databaseContext.Transaction);
            if (driver != null)
            {
                string updateUserSql = SqlCommands.GetUpdateSql("User", new[] { "UserName" }, new[] { "UserId" });
                parameters.Add("?UserId", entity.UserId, DbType.Int64);
                parameters.Add("?UserName", entity.Name, DbType.AnsiString, size: 20);
                return await this.databaseContext.Master.ExecuteAsync(updateUserSql, parameters, databaseContext.Transaction) > 0;
            }
            return false;
        }

        /// <summary>
        /// 司机安全培训考试通过
        /// </summary>
        /// <param name="driverId"></param>
        /// <param name=""></param>
        /// <returns></returns>
        public async Task<DriverEntity> LearnTrainPassedAsync(long driverId)
        {
            string sql = string.Format(@"UPDATE Driver SET  IsLearnTrainPassed=?IsLearnTrainPassed,LearnTrainPassedTime=?LearnTrainPassedTime  
                                  WHERE DriverId=?DriverId ;{0} WHERE DriverId=?DriverId ;", QUERY);

            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?IsLearnTrainPassed", true, DbType.Boolean);
            parameter.Add("?LearnTrainPassedTime", DateTime.Now, DbType.DateTime);
            parameter.Add("?DriverId", driverId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameter, databaseContext.Transaction);
        }

        public async Task<DriverEntity> LearnTrainSignAsync(long driverId,bool isLearnTrainSign)
        {
            string sql = string.Format(@"UPDATE Driver SET  IsLearnTrainSign=?IsLearnTrainSign  
                                  WHERE DriverId=?DriverId ;{0} WHERE DriverId=?DriverId ;", QUERY);

            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?IsLearnTrainSign", isLearnTrainSign, DbType.Boolean);
            parameter.Add("?DriverId", driverId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverEntity>(sql, parameter, databaseContext.Transaction);
        }

        public async Task<IEnumerable<DriverEntity>> GetListByAgentUuidAndDriverRoleType(string agentRecordUuid, DriverRoleType driverRoleType)
        {
            return await databaseContext.Slave.QueryAsync<DriverEntity>($"{QUERY} WHERE AgentRecordUuid = ?AgentRecordUuid AND Role = ?Role AND DriverStatus = ?DriverStatus",
                new { agentRecordUuid, Role = driverRoleType.ToString(), DriverStatus = DriverStatus.Success.ToString() });
        }

        public async Task<IEnumerable<DriverDynamicInfoEntity>> GetDriverDynamicInfo(string agentRecordUuid)
        {
            string sql = @"SELECT WorkingStatus,COUNT(DriverId) AS TotalNumber 
                        FROM driver
                        WHERE AgentRecordUuid = ?AgentRecordUuid AND DriverStatus = ?DriverStatus
                        GROUP BY WorkingStatus ";
            return await databaseContext.Slave.QueryAsync<DriverDynamicInfoEntity>(sql, new { agentRecordUuid, DriverStatus = DriverStatus.Success.ToString() });
        }

        public async Task<IEnumerable<DriverDynamicDetailEntity>> GetDriverDynamicDetail(string agentRecordUuid)
        {
            string sql = @"SELECT A.DriverId,A.Name AS DriverName,A.Role,A.WorkingStatus,COUNT(B.OrderId)/30 AS AverageDailySingular
                        FROM Driver A
                        LEFT JOIN Orders B ON A.DriverId = B.DriverId AND B.OrderStatus = ?OrderStatus AND DATE(B.CompleteTime) >= DATE_ADD(CURDATE(),INTERVAL -30 DAY)
                        WHERE A.AgentRecordUuid = ?AgentRecordUuid 
                        AND A.DriverStatus = ?DriverStatus 
                        GROUP BY A.DriverId";
            return await databaseContext.Slave.QueryAsync<DriverDynamicDetailEntity>(sql, new { agentRecordUuid, DriverStatus = DriverStatus.Success.ToString(), OrderStatus = OrderStatus.Complete.ToString() });
        }

        public async Task<int> GetRealNameVerifiesCount(string agentRecordUuid, DriverRoleType roleType, RealNameVerifiesStatus realNameVerifiesStatus)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(agentRecordUuid))
            {
                wheres.Add("AgentRecordUuid = ?AgentRecordUuid");
                parameters.Add("?AgentRecordUuid", agentRecordUuid, DbType.AnsiString);
            }
            wheres.Add("Role = ?Role");
            parameters.Add("?Role", roleType.ToString(), DbType.AnsiString);
            wheres.Add("RealNameVerifiesStatus = ?RealNameVerifiesStatus");
            parameters.Add("?RealNameVerifiesStatus", realNameVerifiesStatus.ToString(), DbType.AnsiString);
            string sql = string.Format("SELECT COUNT(1) FROM Driver {0}", wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await databaseContext.Slave.QueryEntityAsync<int>(sql, parameters);
        }

        public async Task<int> GetVehiclePendingAuditCount(string agentRecordUuid, DriverRoleType roleType, DriverStatus driverStatus)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(agentRecordUuid))
            {
                wheres.Add("AgentRecordUuid = ?AgentRecordUuid");
                parameters.Add("?AgentRecordUuid", agentRecordUuid, DbType.AnsiString);
            }
            wheres.Add("Role = ?Role");
            parameters.Add("?Role", roleType.ToString(), DbType.AnsiString);
            wheres.Add("DriverStatus <> ?DriverStatus");
            parameters.Add("?DriverStatus", driverStatus.ToString(), DbType.AnsiString);
            string sql = string.Format("SELECT COUNT(1) FROM Driver {0}", wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await databaseContext.Slave.QueryEntityAsync<int>(sql, parameters);
        }
    }
}
